#!/usr/bin/env python
# -*- coding: utf-8 -*-

''' Author: Susana Messias n.º 6046 '''

import csv
from com.ziclix.python.sql import zxJDBC

class createDatabase:
    '''
    Class responsible for the convertion of the csv files into sqlite database.
    '''
    @staticmethod
    def create():
        
    

        # List with the csv file names. 
        listaCsv = ['rebcsv/reb0.csv',
                    'rebcsv/reb1.csv',
                    'rebcsv/reb2.csv',
                    'rebcsv/reb3.csv',
                    'rebcsv/reb4.csv',
                    'rebcsv/reb5.csv',
                    'rebcsv/reb6.csv',
                    'rebcsv/reb7.csv',
                    'rebcsv/reb8.csv',
                    'rebcsv/reb9.csv',
                    'rebcsv/reb10.csv']
        lista = []
        nomes = []

        conexao = zxJDBC.connect("jdbc:sqlite:tabela.db", '', '', "org.sqlite.JDBC")
        c = conexao.cursor(1)

        ######## For not duplicate the table #########
        c.execute("drop table if exists tabela")
        conexao.commit()
        #############################################


        c.execute("create table if not exists tabela (Codigo_do_Docente text, Nome text, Ultimo_Grau text, Curso text, Categoria text, Regime_de_tempo text, Ano_Lectivo text, Tipo_de_Estabelecimento text, Nome_do_Estabelecimento text)")

        for csvFile in listaCsv:
            csvReader = csv.reader(open(csvFile, 'rb'), delimiter=',')
            line = 0
            
            for row in csvReader:
                lista2 = []
                
                if line == 0:
                    for coluna in row:
                        lista2.append(str(coluna))
                        pass
                    nomes = lista2            
                    line = 1

                    pass
                else:
                    stmt = "insert into tabela values(?, ?, ?, ?, ?, ?, ?, ?, ?)"
                    
                    c.executemany(stmt, [ str(row[0]).replace( '"', '♦' ).decode('utf-8'),
                                        str(row[1]).replace( '"', '♦' ).decode('utf-8'),
                                        str(row[2]).replace( '"', '♦' ).decode('utf-8'),
                                        str(row[3]).replace( '"', '♦' ).decode('utf-8'),
                                        str(row[4]).replace( '"', '♦' ).decode('utf-8'),
                                        str(row[5]).replace( '"', '♦' ).decode('utf-8'),
                                        str(row[6]).replace( '"', '♦' ).decode('utf-8'),
                                        str(row[7]).replace( '"', '♦' ).decode('utf-8'),
                                        str(row[8]).replace( '"', '♦' ).decode('utf-8') ])
                pass
            pass
        # The end

        
        conexao.commit()
        conexao.close()
            


